import json
import os
import time

import pandas as pd
from pymongo import MongoClient

from bqtool.utils.tools import to_pdtime_ms

columns = ['VERSION', 'PASSTIME', 'CARSTATE', 'CARPLATE', 'PLATETYPE', 'SPEED', 'PLATECOLOR', 'LOCATIONID'
    , 'DEVICEID', 'DRIVEWAY', 'DRIVEDIR', 'CAPTUREDIR', 'CARCOLOR', 'CARBRAND', 'CARBRANDZW', 'TGSID', 'PLATECOORD'
    , 'CABCOORD', 'IMGID1', 'IMGID2', 'IMGID3', 'IMGID4', 'IMGID5']

columns_use = ['PASSTIME', 'CARPLATE', 'SPEED', 'DRIVEWAY', 'DRIVEDIR', 'CAPTUREDIR', 'PLATETYPE', 'CARCOLOR', 'TGSID']


def file_to_df(file):
    print('converting file:%r(%r MB)' % (file, (os.path.getsize(file) / (1024 * 1024))))
    df_org = pd.read_csv(file, sep='\t', header=None, names=columns)
    df = df_org[columns_use].copy()
    for k in df.columns:
        _, df[k] = df[k].astype(str).str.split('=', 1).str

    df['PASSTIME'] = df['PASSTIME'].astype(str).str.replace(' ', '?', n=1)
    df['PASSTIME'] = df['PASSTIME'].astype(str).str.replace(' ', '.', n=1)
    df['PASSTIME'] = df['PASSTIME'].astype(str).str.replace('?', ' ', n=1)

    # 因为PASSTIME中有些数据是错误的，例如PASSTIME=2017-06-01VERSION=1.0，直接使用df['PASSTIME'].astype('datetime64[ns]')
    # 可能导致程序抛出valueError: Error parsing datetime string "2017-06-01VERSION=1.0" at position 10
    # 因此使用了to_datetime函数，使无法解析的字符串转换为NaT，然后过滤NaT数据即可
    df['PASSTIME'] = pd.to_datetime(df['PASSTIME'], errors='coerce')

    # 关于性能问题， https://stackoverflow.com/questions/46091924/python-how-to-drop-a-row-whose-particular-column-is-empty-nan/46091980#46091980
    # 上面的帖子给出了非常实用的统计指导。
    df = df[df['PASSTIME'].notnull()]
    df['PASSTIME'] =  df['PASSTIME'].dt.tz_localize('Asia/Shanghai')
    # df = df[~df['PASSTIME'].isin([pd.NaT])]

    # df = df.set_index('PASSTIME')
    #   请记得，一定要有赋值操作，否则更改的就没有意义

    # df.index = df.index.tz_localize('Asia/Shanghai')

    df['DRIVEDIR'] = df['DRIVEDIR'].astype('int32', errors='ignore')
    df['DRIVEWAY'] = df['DRIVEWAY'].astype('int32', errors='ignore')

    if (not pd.api.types.is_integer_dtype(df['DRIVEDIR'])):
        df['DRIVEDIR'] = pd.to_numeric(df['DRIVEDIR'], errors='coerce')
        df = df[df['DRIVEDIR'].notnull()].copy()
        df['DRIVEDIR'] = df['DRIVEDIR'].astype('int32')

    if (not pd.api.types.is_integer_dtype(df['DRIVEWAY'])):
        df['DRIVEWAY'] = pd.to_numeric(df['DRIVEWAY'], errors='coerce')
        df = df[df['DRIVEWAY'].notnull()].copy()
        df['DRIVEWAY'] = df['DRIVEWAY'].astype('int32')
    mongo.collection.insert_many(json.loads(df.T.to_json()).values())
    # df_to_mongodb(df)
    # return df


def read_file():
    dir = r'D:\heze\data\tvc'
    array = []
    for root, dirs, files in os.walk(dir):
        for file in files:
            dir = os.path.join(root,file)
            file_to_df(dir)
            # array.append(df)
    # all = pd.concat(array)
    # set('pasccar2017/06/01-09-10', all)


def df_to_mongodb(df,mongoc):
    # file = r'D:\heze\d/20.0.51.56-tvc-2017-06-09-08'
    # df = file_to_df(file)
    # df = get('mongodbtest')
    # df = df.head()
    # mongo = MongoBase('passcar')
    # start = time.clock()
    mongoc.collection.insert_many(json.loads(df.T.to_json()).values())
    # mongo.collection.insert(json.loads(df.T.to_json()).values())
    # end = time.clock()
    # print('Running time: %s Seconds' % (end - start))
    # mongo.closeDB()
    print(len(df))

def find():
    startTime = pd.to_datetime('2017/06/30 21:00:00')
    endTime = pd.to_datetime('2017/06/30 21:30:00')
    start = int(startTime.tz_localize('Asia/Shanghai').value/10**6)
    end = int(endTime.tz_localize('Asia/Shanghai').value/10**6)
    data = mongo.collection.find({"PASSTIME": {"$gte": start, "$lt": end}})
    data = pd.DataFrame(list(data))
    if len(data)>0:
        data['PASSTIME'] = to_pdtime_ms(data['PASSTIME'])
    print('ok')


class MongoBase:
    def __init__(self,collection,db='ai'):
        self.collection=collection
        self.dbname = db
        self.OpenDB()
    def OpenDB(self):
        # user='******'
        # passwd='******'
        # host='******'
        # port='******'
        # auth_db='******'
        # uri = "mongodb://"+user+":"+passwd+"@"+host+":"+port+"/"+auth_db+"?authMechanism=SCRAM-SHA-1"
        self.con = MongoClient('10.2.111.241', 27017)
        self.db=self.con[self.dbname]
        self.collection=self.db[self.collection]
    def closeDB(self):
        self.con.close()

mongo = MongoBase('passcar')

if __name__ == '__main__':
    # mongo.collection.create_index('PASSTIME')
    # print('ok')
    # print(mongo.collection.index_information())
    # read_file()
    find()
